package controller;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;

public class OracleProcedure {

	public static final int INSERT = 1;
	public static final int UPDATE = 2;
	public static final int DELETE = 3;
	public static final int SELECT = 4;
	public static final int SELECT_FUNC = 5;
	
	private int choice;
	private String tableName;
	private CallableStatement callstmt;
	private ResultSet cursor;
	private String column;
	
	public OracleProcedure(int choice, String tableName)
	{
		this.choice = choice;
		setTableName(tableName);
	}
	
	public void make(Object...objects)
	{
		String sqlString = "";
		try {
			switch (choice) {
			case INSERT: {
				sqlString = "begin System." + tableName + "_Insert("
						+ prepareSqlStatementArgs(objects.length) + "); end;";
				prepare(sqlString, objects);
				break;
			}
			case UPDATE: {
				sqlString = "begin System." + tableName + "_Update("
						+ prepareSqlStatementArgs(objects.length) + "); end;";
				prepare(sqlString, objects);
				break;
			}
			case DELETE: {
				sqlString = "begin System." + tableName + "_Delete(?); end;";
				prepare(sqlString, objects);
				break;
			}
			case SELECT: {
				sqlString = "begin open ? for select * from "
						+ tableName
						+ ((column != null) ? " where " + column + "=?; end;"
								: "; end;");
				prepare(sqlString, objects);
				cursor = ((OracleCallableStatement) callstmt).getCursor(1);
				break;
			}
			case SELECT_FUNC: {
				sqlString = "begin ? := " + tableName + "_ALL; end;";
				prepare(sqlString, objects);
				cursor = ((OracleCallableStatement) callstmt).getCursor(1);
				break;
			}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private String prepareSqlStatementArgs(int size)
	{
		String s = "";
		for(int i=0; i<size; i++)
		{
			s+="?,";
		}
		return s.substring(0, s.length()-1);
	}
	
	private void prepare(String sqlString, Object...objects) throws SQLException
	{
		OracleDataSource ods = new OracleDataSource();
		ods.setURL("jdbc:oracle:thin:@localhost:1521:xe");
		ods.setUser("system");
		ods.setPassword("admin");
		Connection conn = ods.getConnection();
		callstmt = conn.prepareCall(sqlString);
		prepareArgs(objects);
		callstmt.execute();
	}
	
	private void prepareArgs(Object...objects) throws SQLException
	{
		int index = 1;
		for(Object o:objects)
		{
			if(o instanceof Integer)
			{
				callstmt.setInt(index,(Integer)o);
			}
			else if(o instanceof String)
			{
				callstmt.setString(index,(String)o);
			}
			else if(o instanceof Boolean)
			{
				callstmt.registerOutParameter(1, OracleTypes.CURSOR);
			}
			else if(o instanceof Date)
			{
				callstmt.setDate(index, (Date)o);
			}
			index++;
		}
	}

	public ResultSet getCursor() {
		return cursor;
	}

	public void setCursor(ResultSet cursor) {
		this.cursor = cursor;
	}

	/**
	 * @return the tableName
	 */
	public String getTableName() {
		return tableName;
	}

	/**
	 * @param tableName the tableName to set
	 */
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getColumn() {
		return column;
	}

	public void setColumn(String column) {
		this.column = column;
	}
}
